﻿IF OBJECT_ID(N'[dbo].[MigrationHistory]') IS NULL
BEGIN
    CREATE TABLE [dbo].[MigrationHistory] (
        [MigrationId] nvarchar(150) NOT NULL,
        [ProductVersion] nvarchar(32) NOT NULL,
        CONSTRAINT [PK_MigrationHistory] PRIMARY KEY ([MigrationId])
    );
END;

GO

CREATE TABLE [Account] (
    [Id] int NOT NULL IDENTITY,
    [EntityStatus] int NOT NULL,
    [CreateTime] datetime2 NULL,
    [CreateBy] nvarchar(200) NULL,
    [UpdateTime] datetime2 NULL,
    [UpdateBy] nvarchar(200) NULL,
    [UserName] nvarchar(20) NOT NULL,
    [Password] nvarchar(100) NOT NULL,
    [NickName] nvarchar(200) NULL,
    [Name] nvarchar(200) NULL,
    CONSTRAINT [PK_Account] PRIMARY KEY ([Id])
);

GO

CREATE TABLE [AuthToken] (
    [Id] int NOT NULL IDENTITY,
    [UserName] nvarchar(200) NULL,
    [AccessToken] nvarchar(1024) NULL,
    [RefreshToken] nvarchar(1024) NULL,
    [UpdateTime] datetime2 NULL DEFAULT (GETDATE()),
    [CreateTime] datetime2 NULL DEFAULT (GETDATE()),
    CONSTRAINT [PK_AuthToken] PRIMARY KEY ([Id])
);

GO

CREATE TABLE [BuryPointLog] (
    [Id] int NOT NULL IDENTITY,
    [EndPoint] nvarchar(max) NULL,
    [ClientIp] nvarchar(max) NULL,
    [ClientAgent] nvarchar(max) NULL,
    [RequestURL] nvarchar(max) NULL,
    [LogTime] datetime2 NULL,
    CONSTRAINT [PK_BuryPointLog] PRIMARY KEY ([Id])
);

GO

CREATE TABLE [Company] (
    [Id] int NOT NULL IDENTITY,
    [EntityStatus] int NOT NULL,
    [CreateTime] datetime2 NULL,
    [CreateBy] nvarchar(200) NULL,
    [UpdateTime] datetime2 NULL,
    [UpdateBy] nvarchar(200) NULL,
    [Name] nvarchar(200) NOT NULL,
    [Address_Country_Name] nvarchar(200) NULL,
    [Address_Country_Address] nvarchar(500) NULL,
    [Address_Country_Continent] nvarchar(25) NULL,
    [Address_Province] nvarchar(25) NULL,
    [Address_City] nvarchar(25) NULL,
    [Address_Region] nvarchar(500) NULL,
    [Address_Street] nvarchar(500) NULL,
    [Address_DetailAddress] nvarchar(1024) NULL,
    [Fix] nvarchar(25) NULL,
    CONSTRAINT [PK_Company] PRIMARY KEY ([Id])
);

GO

CREATE TABLE [Department] (
    [Id] int NOT NULL IDENTITY,
    [EntityStatus] int NOT NULL,
    [CreateTime] datetime2 NULL,
    [CreateBy] nvarchar(200) NULL,
    [UpdateTime] datetime2 NULL,
    [UpdateBy] nvarchar(200) NULL,
    [Name] nvarchar(1024) NULL,
    [DepartmentNo] nvarchar(1024) NULL,
    CONSTRAINT [PK_Department] PRIMARY KEY ([Id])
);

GO

CREATE TABLE [Dicts] (
    [Id] int NOT NULL IDENTITY,
    [EntityStatus] int NOT NULL,
    [CreateTime] datetime2 NULL,
    [CreateBy] nvarchar(200) NULL,
    [UpdateTime] datetime2 NULL,
    [UpdateBy] nvarchar(200) NULL,
    [Name] nvarchar(50) NULL,
    [Value] nvarchar(2000) NULL,
    [Category] nvarchar(50) NULL,
    CONSTRAINT [PK_Dicts] PRIMARY KEY ([Id])
);

GO

CREATE TABLE [ExceptionLog] (
    [Id] int NOT NULL IDENTITY,
    [ExceptionType] int NOT NULL,
    [MachineName] nvarchar(200) NULL,
    [Logger] nvarchar(200) NULL,
    [Message] nvarchar(max) NULL,
    [Callsite] nvarchar(max) NULL,
    [Exception] nvarchar(max) NULL,
    [LogTime] datetime2 NULL,
    CONSTRAINT [PK_ExceptionLog] PRIMARY KEY ([Id])
);

GO

CREATE TABLE [Navigation] (
    [Id] int NOT NULL IDENTITY,
    [EntityStatus] int NOT NULL,
    [CreateTime] datetime2 NULL,
    [CreateBy] nvarchar(200) NULL,
    [UpdateTime] datetime2 NULL,
    [UpdateBy] nvarchar(200) NULL,
    [ParentId] int NULL,
    [Name] nvarchar(400) NULL,
    [SerialNum] int NOT NULL,
    [Url] nvarchar(200) NULL,
    [Icon] nvarchar(100) NULL,
    CONSTRAINT [PK_Navigation] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_Navigation_Navigation_ParentId] FOREIGN KEY ([ParentId]) REFERENCES [Navigation] ([Id])
);

GO

CREATE TABLE [Permission] (
    [Id] int NOT NULL IDENTITY,
    [EntityStatus] int NOT NULL,
    [CreateTime] datetime2 NULL,
    [CreateBy] nvarchar(200) NULL,
    [UpdateTime] datetime2 NULL,
    [UpdateBy] nvarchar(200) NULL,
    [Name] nvarchar(200) NULL,
    CONSTRAINT [PK_Permission] PRIMARY KEY ([Id])
);

GO

CREATE TABLE [Role] (
    [Id] int NOT NULL IDENTITY,
    [EntityStatus] int NOT NULL,
    [CreateTime] datetime2 NULL,
    [CreateBy] nvarchar(200) NULL,
    [UpdateTime] datetime2 NULL,
    [UpdateBy] nvarchar(200) NULL,
    [RoleName] nvarchar(50) NULL,
    [Description] nvarchar(500) NULL,
    CONSTRAINT [PK_Role] PRIMARY KEY ([Id])
);

GO

CREATE TABLE [CompanyDepartment] (
    [CompanyId] int NOT NULL,
    [DepartmentId] int NOT NULL,
    CONSTRAINT [PK_CompanyDepartment] PRIMARY KEY ([DepartmentId], [CompanyId]),
    CONSTRAINT [FK_CompanyDepartment_Company_CompanyId] FOREIGN KEY ([CompanyId]) REFERENCES [Company] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_CompanyDepartment_Department_DepartmentId] FOREIGN KEY ([DepartmentId]) REFERENCES [Department] ([Id]) ON DELETE CASCADE
);

GO

CREATE TABLE [Employee] (
    [Id] int NOT NULL IDENTITY,
    [EntityStatus] int NOT NULL,
    [CreateTime] datetime2 NULL,
    [CreateBy] nvarchar(200) NULL,
    [UpdateTime] datetime2 NULL,
    [UpdateBy] nvarchar(200) NULL,
    [Name] nvarchar(100) NOT NULL,
    [EmployeeNo] nvarchar(100) NULL,
    [Contact] nvarchar(50) NULL,
    [Gender] nvarchar(max) NOT NULL,
    [CompanyId] int NULL,
    [DepartmentId] int NOT NULL,
    CONSTRAINT [PK_Employee] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_Employee_Company_CompanyId] FOREIGN KEY ([CompanyId]) REFERENCES [Company] ([Id]) ON DELETE NO ACTION,
    CONSTRAINT [FK_Employee_Department_DepartmentId] FOREIGN KEY ([DepartmentId]) REFERENCES [Department] ([Id]) ON DELETE CASCADE
);

GO

CREATE TABLE [PermissionNavigation] (
    [NavigationId] int NOT NULL,
    [PermissionId] int NOT NULL,
    CONSTRAINT [PK_PermissionNavigation] PRIMARY KEY ([NavigationId], [PermissionId]),
    CONSTRAINT [FK_PermissionNavigation_Navigation_NavigationId] FOREIGN KEY ([NavigationId]) REFERENCES [Navigation] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_PermissionNavigation_Permission_PermissionId] FOREIGN KEY ([PermissionId]) REFERENCES [Permission] ([Id]) ON DELETE CASCADE
);

GO

CREATE TABLE [AccountRole] (
    [AccountId] int NOT NULL,
    [RoleId] int NOT NULL,
    CONSTRAINT [PK_AccountRole] PRIMARY KEY ([AccountId], [RoleId]),
    CONSTRAINT [FK_AccountRole_Account_AccountId] FOREIGN KEY ([AccountId]) REFERENCES [Account] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_AccountRole_Role_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [Role] ([Id]) ON DELETE CASCADE
);

GO

CREATE TABLE [RolePermission] (
    [RoleId] int NOT NULL,
    [PermissionId] int NOT NULL,
    CONSTRAINT [PK_RolePermission] PRIMARY KEY ([RoleId], [PermissionId]),
    CONSTRAINT [FK_RolePermission_Permission_PermissionId] FOREIGN KEY ([PermissionId]) REFERENCES [Permission] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_RolePermission_Role_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [Role] ([Id]) ON DELETE CASCADE
);

GO

IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'CreateBy', N'CreateTime', N'EntityStatus', N'Name', N'NickName', N'Password', N'UpdateBy', N'UpdateTime', N'UserName') AND [object_id] = OBJECT_ID(N'[Account]'))
    SET IDENTITY_INSERT [Account] ON;
INSERT INTO [Account] ([Id], [CreateBy], [CreateTime], [EntityStatus], [Name], [NickName], [Password], [UpdateBy], [UpdateTime], [UserName])
VALUES (1, N'System', '2020-04-23T20:32:42.4194326+08:00', 0, N'窦子', N'干金', N'E3AFED0047B08059D0FADA10F400C1E5', N'System', '2020-04-23T20:32:42.4195239+08:00', N'Admin');
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'CreateBy', N'CreateTime', N'EntityStatus', N'Name', N'NickName', N'Password', N'UpdateBy', N'UpdateTime', N'UserName') AND [object_id] = OBJECT_ID(N'[Account]'))
    SET IDENTITY_INSERT [Account] OFF;

GO

IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Category', N'CreateBy', N'CreateTime', N'EntityStatus', N'Name', N'UpdateBy', N'UpdateTime', N'Value') AND [object_id] = OBJECT_ID(N'[Dicts]'))
    SET IDENTITY_INSERT [Dicts] ON;
INSERT INTO [Dicts] ([Id], [Category], [CreateBy], [CreateTime], [EntityStatus], [Name], [UpdateBy], [UpdateTime], [Value])
VALUES (1, N'SystemConfig', N'System', '2020-04-23T20:32:42.4236000+08:00', 0, N'RootMenu', N'System', '2020-04-23T20:32:42.4236008+08:00', N'RootMenu'),
(2, N'SystemConfig', N'System', '2020-04-23T20:32:42.4236065+08:00', 0, N'IconUrl', N'System', '2020-04-23T20:32:42.4236066+08:00', N'//at.alicdn.com/t/font_1718503_1fgggozkybt.js');
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'Category', N'CreateBy', N'CreateTime', N'EntityStatus', N'Name', N'UpdateBy', N'UpdateTime', N'Value') AND [object_id] = OBJECT_ID(N'[Dicts]'))
    SET IDENTITY_INSERT [Dicts] OFF;

GO

IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'CreateBy', N'CreateTime', N'EntityStatus', N'Icon', N'Name', N'ParentId', N'SerialNum', N'UpdateBy', N'UpdateTime', N'Url') AND [object_id] = OBJECT_ID(N'[Navigation]'))
    SET IDENTITY_INSERT [Navigation] ON;
INSERT INTO [Navigation] ([Id], [CreateBy], [CreateTime], [EntityStatus], [Icon], [Name], [ParentId], [SerialNum], [UpdateBy], [UpdateTime], [Url])
VALUES (1, N'System', '2020-04-23T20:32:42.4224388+08:00', 0, N'm-home', N'首页', NULL, 1, N'System', '2020-04-23T20:32:42.4224396+08:00', N'/home'),
(2, N'System', '2020-04-23T20:32:42.4224470+08:00', 0, N'm-project-manage', N'项目管理', NULL, 2, N'System', '2020-04-23T20:32:42.4224472+08:00', N'/project'),
(3, N'System', '2020-04-23T20:32:42.4224475+08:00', 0, N'm-user-manage', N'相关方管理', NULL, 3, N'System', '2020-04-23T20:32:42.4224476+08:00', N'/user'),
(4, N'System', '2020-04-23T20:32:42.4224477+08:00', 0, N'm-resources-manage', N'资源管理', NULL, 4, N'System', '2020-04-23T20:32:42.4224478+08:00', N'/project'),
(5, N'System', '2020-04-23T20:32:42.4224480+08:00', 0, N'm-risk-manage', N'风险管理', NULL, 5, N'System', '2020-04-23T20:32:42.4224481+08:00', N'/project/add'),
(7, N'System', '2020-04-23T20:32:42.4224486+08:00', 0, N'm-log-manage', N'日志管理', NULL, 4, N'System', '2020-04-23T20:32:42.4224487+08:00', N'/admin/role');
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'CreateBy', N'CreateTime', N'EntityStatus', N'Icon', N'Name', N'ParentId', N'SerialNum', N'UpdateBy', N'UpdateTime', N'Url') AND [object_id] = OBJECT_ID(N'[Navigation]'))
    SET IDENTITY_INSERT [Navigation] OFF;

GO

IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'CreateBy', N'CreateTime', N'EntityStatus', N'Name', N'UpdateBy', N'UpdateTime') AND [object_id] = OBJECT_ID(N'[Permission]'))
    SET IDENTITY_INSERT [Permission] ON;
INSERT INTO [Permission] ([Id], [CreateBy], [CreateTime], [EntityStatus], [Name], [UpdateBy], [UpdateTime])
VALUES (1, N'System', '2020-04-23T20:32:42.4228323+08:00', 0, N'修改', N'System', '2020-04-23T20:32:42.4228330+08:00');
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'CreateBy', N'CreateTime', N'EntityStatus', N'Name', N'UpdateBy', N'UpdateTime') AND [object_id] = OBJECT_ID(N'[Permission]'))
    SET IDENTITY_INSERT [Permission] OFF;

GO

IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'CreateBy', N'CreateTime', N'Description', N'EntityStatus', N'RoleName', N'UpdateBy', N'UpdateTime') AND [object_id] = OBJECT_ID(N'[Role]'))
    SET IDENTITY_INSERT [Role] ON;
INSERT INTO [Role] ([Id], [CreateBy], [CreateTime], [Description], [EntityStatus], [RoleName], [UpdateBy], [UpdateTime])
VALUES (1, N'System', '2020-04-23T20:32:42.4211452+08:00', N'系统管理员', 0, N'Administrator', N'System', '2020-04-23T20:32:42.4211462+08:00'),
(2, N'System', '2020-04-23T20:32:42.4211515+08:00', N'默认用户权限', 0, N'Default', N'System', '2020-04-23T20:32:42.4211517+08:00');
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'CreateBy', N'CreateTime', N'Description', N'EntityStatus', N'RoleName', N'UpdateBy', N'UpdateTime') AND [object_id] = OBJECT_ID(N'[Role]'))
    SET IDENTITY_INSERT [Role] OFF;

GO

IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'AccountId', N'RoleId') AND [object_id] = OBJECT_ID(N'[AccountRole]'))
    SET IDENTITY_INSERT [AccountRole] ON;
INSERT INTO [AccountRole] ([AccountId], [RoleId])
VALUES (1, 1);
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'AccountId', N'RoleId') AND [object_id] = OBJECT_ID(N'[AccountRole]'))
    SET IDENTITY_INSERT [AccountRole] OFF;

GO

IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'CreateBy', N'CreateTime', N'EntityStatus', N'Icon', N'Name', N'ParentId', N'SerialNum', N'UpdateBy', N'UpdateTime', N'Url') AND [object_id] = OBJECT_ID(N'[Navigation]'))
    SET IDENTITY_INSERT [Navigation] ON;
INSERT INTO [Navigation] ([Id], [CreateBy], [CreateTime], [EntityStatus], [Icon], [Name], [ParentId], [SerialNum], [UpdateBy], [UpdateTime], [Url])
VALUES (8, N'System', '2020-04-23T20:32:42.4224916+08:00', 0, N'm-add-project', N'添加项目', 2, 5, N'System', '2020-04-23T20:32:42.4224921+08:00', N'/project/add');
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'Id', N'CreateBy', N'CreateTime', N'EntityStatus', N'Icon', N'Name', N'ParentId', N'SerialNum', N'UpdateBy', N'UpdateTime', N'Url') AND [object_id] = OBJECT_ID(N'[Navigation]'))
    SET IDENTITY_INSERT [Navigation] OFF;

GO

IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'RoleId', N'PermissionId') AND [object_id] = OBJECT_ID(N'[RolePermission]'))
    SET IDENTITY_INSERT [RolePermission] ON;
INSERT INTO [RolePermission] ([RoleId], [PermissionId])
VALUES (1, 1);
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'RoleId', N'PermissionId') AND [object_id] = OBJECT_ID(N'[RolePermission]'))
    SET IDENTITY_INSERT [RolePermission] OFF;

GO

CREATE UNIQUE INDEX [IX_Account_UserName] ON [Account] ([UserName]);

GO

CREATE INDEX [IX_AccountRole_RoleId] ON [AccountRole] ([RoleId]);

GO

CREATE INDEX [IX_CompanyDepartment_CompanyId] ON [CompanyDepartment] ([CompanyId]);

GO

CREATE INDEX [IX_Employee_CompanyId] ON [Employee] ([CompanyId]);

GO

CREATE INDEX [IX_Employee_DepartmentId] ON [Employee] ([DepartmentId]);

GO

CREATE INDEX [IX_Navigation_ParentId] ON [Navigation] ([ParentId]);

GO

CREATE INDEX [IX_PermissionNavigation_PermissionId] ON [PermissionNavigation] ([PermissionId]);

GO

CREATE INDEX [IX_RolePermission_PermissionId] ON [RolePermission] ([PermissionId]);

GO

INSERT INTO [dbo].[MigrationHistory] ([MigrationId], [ProductVersion])
VALUES (N'20200423123242_init', N'3.1.3');

GO

